﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_ZUTEIEX(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_ZUTEIEX(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_ZUTEIEX(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_ZUTEIEX(ref dwLocal, dwData);

            if (!flgExist)
            {
                dwLocal["ROWNID"] = GetSeq();
                InsertICC_ZUTEIEX(dwLocal);
            }
            UpdateICC_ZUTEIEX(dwLocal);
        }
        public void InsertICC_ZUTEIEX(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_ZUTEIGP select ZULO,iccdat,ABPART,ABPVAR,ZUART,ZUVAR,SUM(ZUEINTKG) AS QTYKG,'QT00' AS QTYPE from abpos a,icc_zutei b,icc_gllg c where a.abpmandant = b.zumandant and a.abpaufkrs = b.zuaufkrs and a.abpauflfd = b.zuauflfd and a.abpaufpos = b.zuaufpos "
                    + " and b.iccdat =:SETDATE AND B.ICCDAT = C.ICCGLDAT AND B.ZUMANDANT = C.ICCGLMANDANT AND B.ZUPARTIE = C.ICCGLPARTIE AND B.ZUPARTIEKZ = C.ICCGLPARTIEKZ AND B.ZULO = C.ICCGLLO AND B.ZULB = C.ICCGLLB AND B.ZULBEHI = C.ICCGLLBEHI AND NOT EXISTS (SELECT 1 FROM  ICC_ZUTEIGP Z WHERE Z.iccdat =:SETDATE AND QTYPE='QT00' AND Z.ZULO=B.ZULO AND Z.iccdat=B.ICCDAT AND Z.ABPART=A.ABPART AND Z.ABPVAR=A.ABPVAR AND Z.ZUART= B.ZUART AND Z.ZUVAR=B.ZUVAR) GROUP BY ZULO,iccdat,ABPART,ABPVAR,ZUART,ZUVAR";
                    or1.Parameters.Add("SETDATE", OracleType.NVarChar).Value = dwData["SETDATE"].ToString();
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO ICC_ZUTEIGP select ZULO,iccdat,ABPART,ABPVAR,ZUART,ZUVAR,SUM(ZUEINTKG) AS QTYKG,'QT99' AS QTYPE from abpos a,icc_zutei b,icc_gllg c where a.abpmandant = b.zumandant and a.abpaufkrs = b.zuaufkrs and a.abpauflfd = b.zuauflfd and a.abpaufpos = b.zuaufpos "
                    + " and b.iccdat =:SETDATE AND B.ICCDAT = C.ICCGLDAT AND B.ZUMANDANT = C.ICCGLMANDANT AND B.ZUPARTIE = C.ICCGLPARTIE AND B.ZUPARTIEKZ = C.ICCGLPARTIEKZ AND B.ZULO = C.ICCGLLO AND B.ZULB = C.ICCGLLB AND B.ZULBEHI = C.ICCGLLBEHI AND NOT EXISTS (SELECT 1 FROM  ICC_ZUTEIGP Z WHERE Z.iccdat =:SETDATE AND QTYPE='QT99' AND Z.ZULO=B.ZULO AND Z.iccdat=B.ICCDAT AND Z.ABPART=A.ABPART AND Z.ABPVAR=A.ABPVAR AND Z.ZUART= B.ZUART AND Z.ZUVAR=B.ZUVAR) GROUP BY ZULO,iccdat,ABPART,ABPVAR,ZUART,ZUVAR";
                    or1.Parameters.Add("SETDATE", OracleType.NVarChar).Value = dwData["SETDATE"].ToString();
                    break;
                case 3:
                    or1.CommandText = "INSERT INTO ICC_ZUTEIGP    SELECT GABLO,SUBSTR(GABBUCHDAT,1,6),ABPART,ABPVAR,GABART,GABVAR,SUM(GABKG),'QT10' FROM ICC_MGABEW M, ABPOS A  WHERE SUBSTR(GABBUCHDAT, 1, 6)=:MONTH AND GABAUFKRS=ABPAUFKRS AND GABAUFLFD=ABPAUFLFD AND GABAUFPOS=ABPAUFPOS  AND GABLO='FI' AND GABLB='PR' AND GABBS IN ('01','10','11','13','21','23','27','41')  AND NOT EXISTS      (SELECT 1 FROM  ICC_ZUTEIGP Z WHERE Z.ICCDAT =:MONTH AND QTYPE='QT10' AND Z.ZULO=M.GABLO AND Z.ABPART=A.ABPART AND Z.ABPVAR=A.ABPVAR AND Z.ZUART= M.GABART AND Z.ZUVAR=M.GABVAR)  GROUP BY GABLO,SUBSTR(GABBUCHDAT,1,6),ABPART,ABPVAR,GABART,GABVAR";
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
                    break;
                case 4:
                    or1.CommandText = "INSERT INTO ICC_ZUTEIGP    SELECT GABLO,SUBSTR(GABBUCHDAT,1,6),ABPART,ABPVAR,GABART,GABVAR,SUM(GABKG),'QT11' FROM ICC_MGABEW M, ABPOS A  WHERE SUBSTR(GABBUCHDAT, 1, 6)=:MONTH AND GABAUFKRS=ABPAUFKRS AND GABAUFLFD=ABPAUFLFD AND GABAUFPOS=ABPAUFPOS  AND GABLO='FI' AND GABLB='PR' AND GABBS IN ('12','20','26','31','33','35','40','50','51','52') AND NOT EXISTS      (SELECT 1 FROM  ICC_ZUTEIGP Z WHERE Z.ICCDAT =:MONTH AND QTYPE='QT11' AND Z.ZULO=M.GABLO AND Z.ABPART=A.ABPART AND Z.ABPVAR=A.ABPVAR AND Z.ZUART= M.GABART AND Z.ZUVAR=M.GABVAR)  GROUP BY GABLO,SUBSTR(GABBUCHDAT,1,6),ABPART,ABPVAR,GABART,GABVAR";
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
                    break; 
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet DeleteICC_ZUTEIEX(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE  FROM ICC_ZUTEIEX";
                    break;
                case 9999:
                    or1.CommandText = "SELECT * FROM ICC_ZUTEIEX";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_ZUTEIEX";
            Return = Result;
            return Return;
        }
        public DataSet QueryICC_ZUTEIEX(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_ZUTEIEX WHERE   ROWNID=:ROWNID"; ;
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    break;
                case 9999:
                    or1.CommandText = "SELECT * FROM ICC_ZUTEIEX";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_ZUTEIEX";
            Return = Result;
            return Return;
        }
        public void UpdateICC_ZUTEIEX(DataRow dwData, int options = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (options)
            {
                case 2:
                    #region====Command====
                    or1.CommandText = "UPDATE ICC_ZUTEIEX SET "
                        + "MACHINEID=:MACHINEID,"
                        + "RGROUP=:RGROUP,"
                        + "JOBV=:JOBV,"
                        + "JOBVDES=:JOBVDES, "
                        + "LOGIC=:LOGIC,"
                        + "RVALUE=:RVALUE,"
                        + "RULEDES=:RULEDES "
                        + " WHERE  "
                        + "   ROWNID=:ROWNID ";
                    #endregion
                    #region====Parameters====
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    or1.Parameters.Add("MACHINEID", OracleType.NVarChar).Value = dwData["MACHINEID"].ToString();
                    or1.Parameters.Add("RGROUP", OracleType.NVarChar).Value = dwData["RGROUP"].ToString();
                    or1.Parameters.Add("JOBV", OracleType.NVarChar).Value = dwData["JOBV"].ToString();
                    or1.Parameters.Add("JOBVDES", OracleType.NVarChar).Value = dwData["JOBVDES"].ToString();
                    or1.Parameters.Add("LOGIC", OracleType.NVarChar).Value = dwData["LOGIC"].ToString();
                    or1.Parameters.Add("RVALUE", OracleType.NVarChar).Value = dwData["RVALUE"].ToString();
                    or1.Parameters.Add("RULEDES", OracleType.NVarChar).Value = dwData["RULEDES"].ToString();
                    #endregion
                    break;
            }

            Connect2Proxy(or1);
        }
    }
}
